package com.my.zone.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.my.talkshow.entity.TalkShow;
import com.my.up.entity.UpShow;
import com.my.zone.entity.Zone;

import util.ConnManager;

public class ZoneDaoImpl implements ZoneDao {

	@Override
	public boolean addOne(Zone zone) {
		boolean flag=false;
		try{
			Connection conn=ConnManager.getConnection();
			String sql="insert into zone (u_id,s_word,add_time) values(?,?,?)";
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setInt(1,zone.getuId() );
			pst.setString(2,zone.getsWord() );
			java.sql.Date date=new java.sql.Date(zone.getAddTime().getTime());
			pst.setDate(3,date);
			pst.execute();
			pst.close();
			conn.close();
			flag=true;
		}catch(Exception e){
			flag=false;
			System.out.println("插入数据出错"+e.getMessage());
		}
		return flag;
	}

	@Override
	public boolean deleteOneById(int id) {
		boolean flag=false;
		Connection conn=null;
		try{
			conn=ConnManager.getConnection();
			String sql="delete from zone where id=?";
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setInt(1,id);
			pst.execute();
			pst.close();
			conn.close();
			flag=true;
		}catch(Exception e){
			flag=false;
			System.out.println("删除数据出错"+e.getMessage());
			try {
				conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				System.out.println("链接关闭出错"+e.getMessage());
			}
		}
		return flag;
	}

	@Override
	public boolean updateOne(Zone zone) {
		boolean flag=false;
		Connection conn=null;
		try{
			conn=ConnManager.getConnection();
			String sql="update zone set s_word=?,c_times=? where s_id=?";
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setString(1, zone.getsWord());
			pst.setInt(2, zone.getcTimes());
			pst.setInt(3, zone.getsId());
			pst.execute();
			pst.close();
			conn.close();
			flag=true;
		}catch(Exception e){
			flag=false;
			try {
				conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				System.out.println("链接关闭出错"+e.getMessage());
			}
			System.out.println("更新说说出错"+e.getMessage());
		}
		return flag;
	}

	@Override
	public List<Zone> selectZone() {
		List<Zone> zoneList=new ArrayList<Zone>();
		Connection conn=null;
		try{
			conn=ConnManager.getConnection();
			String sql="select s_id,u_id,u_nick,s_word,c_times,add_time from zone,user where zone.u_id=user.u_id";
			PreparedStatement pst=conn.prepareStatement(sql);	
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				Zone zone=new Zone();
				zone.setsId(rs.getInt("s_id"));
				zone.setuId(rs.getInt("u_id"));
				zone.setuNick(rs.getString("u_nick"));
				zone.setsWord(rs.getString("s_word"));
				zone.setcTimes(rs.getInt("c_times"));
				zone.setAddTime(rs.getTimestamp("add_time"));
				
				List<UpShow> upList=zone.getUplist();
				String sql2="select u_id,u_nick from user,up where up.u_id=user.u_id and s_id=?";
				PreparedStatement pst2=conn.prepareStatement(sql2);
				pst2.setInt(1,rs.getInt("s_id"));
				ResultSet rs2=pst2.executeQuery();
				while(rs2.next()){
					UpShow upShow =new UpShow();
					upShow.setuId(rs2.getInt("u_id"));
					upShow.setuNick(rs2.getString("u_nick"));
					upList.add(upShow);
				}
				rs2.close();
				pst2.close();
				List<TalkShow> talkList=new ArrayList<TalkShow>();
				String sql3="select u_id,u_nick,talks from user,talk where up.u_id=user.u_id and s_id=?";
				PreparedStatement pst3=conn.prepareStatement(sql3);
				pst2.setInt(1,rs.getInt("s_id"));
				ResultSet rs3=pst3.executeQuery();
				while(rs3.next()){
					TalkShow talkShow =new TalkShow();
					talkShow .setuId(rs3.getInt("u_id"));
					talkShow .setuNick(rs3.getString("u_nick"));
					talkShow.setTalks(rs3.getString("talks"));
					talkList.add(talkShow);
				}
				rs3.close();
				pst3.close();
				
				zoneList.add(zone);
			}
			pst.close();
			conn.close();
			
		}catch(Exception e){
			System.out.println("插入数据出错"+e.getMessage());
			try {
				conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				System.out.println("链接关闭出错"+e.getMessage());
			}
		}
		return zoneList;
	}

}
